Imports System.Collections.Specialized
Imports System.Configuration.Provider
Imports System.Data
Imports System.Data.SqlClient
Imports System.Security.Permissions
Imports System.Web.Configuration

Public Class SqlQuestionnaireTakerProvider
    Inherits QuestionnaireTakerProvider

    ' Fields
    Private _applicationName As String
    Private _connectionString As String
    Private _connectionStringName As String
    Private _CommandTimeout As Integer

    Private _indexUID, _indexQuestionID, _indexInstrumentID, _indexSortID, _indexParentUID, _indexLogic, _indexEnabled As Integer

    Private _nodes As New Dictionary(Of Guid, IQuestionNode)(16)


#Region "Initialized Sub and Properties"

    Public Overrides Sub Initialize(ByVal name As String, ByVal config As NameValueCollection)

        'Verify that config isn't null
        If (config Is Nothing) Then
            Throw New ArgumentNullException("config")
        End If

        'Assign the provider a default name if it doesn't have one
        If String.IsNullOrEmpty(name) Then
            name = "SqlQuestionnaireTakerProvider"
        End If

        'Add a default "description" attribute to config if the
        'Attribute(doesn) 't exist or is empty
        If String.IsNullOrEmpty(config.Item("description")) Then
            config.Remove("description")
            config.Add("description", "SQL Questionnaire Taker Provider")
        End If

        'Call the base class's Initialize method
        MyBase.Initialize(name, config)

        'Initialize _applicationName
        _applicationName = config("applicationName")

        If (String.IsNullOrEmpty(_applicationName)) Then _applicationName = "/"

        config.Remove("applicationName")

        'Initialize _connectionString
        _connectionStringName = config("connectionStringName")

        If (String.IsNullOrEmpty(_connectionStringName)) Then _
            Throw New ProviderException("Empty or missing connectionStringName")

        If WebConfigurationManager.ConnectionStrings(_connectionStringName) Is Nothing Then _
            Throw New ProviderException("Missing connection string")

        _connectionString = WebConfigurationManager.ConnectionStrings(_connectionStringName).ConnectionString()

        If (String.IsNullOrEmpty(_connectionString)) Then _
            Throw New ProviderException("Empty connection string")

        config.Remove("connectionStringName")

        'Retrive a commandTimeout
        Dim _Timeout As String = config("commandTimeout")
        If String.IsNullOrEmpty(_Timeout) Then
            _Timeout = "30"
        End If
        If Not Integer.TryParse(_Timeout, _CommandTimeout) OrElse _CommandTimeout < 0 Then
            Throw New ProviderException("Value must be non negative integer")
        End If

        config.Remove("commandTimeout")

        'Throw an exception if unrecognized attributes remain
        If (config.Count > 0) Then
            Dim attr As String = config.GetKey(0)
            If (Not String.IsNullOrEmpty(attr)) Then
                Throw New ProviderException("Unrecognized attribute: " + attr)
            End If
        End If

    End Sub

    Public Overrides Property ApplicationName() As String
        Get
            Return Me._applicationName
        End Get
        Set(ByVal value As String)
            If String.IsNullOrEmpty(value) Then
                _applicationName = "/"
            End If
            If (value.Length > 256) Then
                Throw New ProviderException("Provider application name is too long")
            End If
            Me._applicationName = value
        End Set
    End Property

    Public Property ConnectionStringName() As String
        Get
            Return _connectionStringName
        End Get
        Set(ByVal value As String)
            If (String.IsNullOrEmpty(value)) Then _
                Throw New ProviderException("Empty or missing connectionStringName")
            _connectionStringName = value
            If WebConfigurationManager.ConnectionStrings(value) Is Nothing Then _
                Throw New ProviderException("Missing connection string for " & value)
            _connectionString = WebConfigurationManager.ConnectionStrings(value).ConnectionString()
            If (String.IsNullOrEmpty(_connectionString)) Then _
                Throw New ProviderException("Empty connection string for " & value)
        End Set
    End Property

#End Region


#Region "Private Subs and Functions"

    Private Function CreateInputParam( _
        ByVal paramName As String, _
        ByVal dbType As SqlDbType, _
        ByVal objValue As Object) As SqlParameter

        Dim p1 As New SqlParameter(paramName, dbType)
        If (objValue Is Nothing) Then
            p1.IsNullable = True
            p1.Value = DBNull.Value
            Return p1
        End If
        p1.Value = objValue
        Return p1
    End Function

    Private Function GetNullableString( _
        ByVal reader As SqlDataReader, _
        ByVal col As Integer) As String
        If reader.IsDBNull(col) Then
            Return String.Empty
        Else
            Return reader.GetString(col)
        End If
    End Function

    Private Function RoundToSeconds(ByVal dt As DateTime) As DateTime
        Return New DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second)
    End Function



    Private Function CreateQuestionNodeFromDataReader(ByVal reader As SqlDataReader) As QuestionNode
        ' Make sure the node ID is present
        If (reader.IsDBNull(_indexUID)) Then Throw New ProviderException("Missing UID for QuestionNode")
        ' Get the node ID from the DataReader
        Dim uid As Guid = reader.GetGuid(_indexUID)
        '  Make sure the node ID is unique
        If (_nodes.ContainsKey(uid)) Then Throw New ProviderException("Duplicate UID for QuestionNode")
        Dim instrumentId As String = reader.GetString(_indexInstrumentID)
        Dim questionId As Integer = reader.GetInt32(_indexQuestionID)
        Dim sortId As Integer = reader.GetByte(_indexSortID)
        Dim node As New QuestionNode(uid, questionId, instrumentId, sortId)
        node.Enabled = reader.GetBoolean(_indexEnabled)
        _nodes.Add(uid, node)
        Return node
    End Function

    Private Function CreateLogicNodeFromDataReader(ByVal reader As SqlDataReader) As LogicNode
        ' Make sure the node ID is present
        If (reader.IsDBNull(_indexUID)) Then Throw New ProviderException("Missing UID for LogicNode")
        ' Get the node ID from the DataReader
        Dim uid As Guid = reader.GetGuid(_indexUID)
        '  Make sure the node ID is unique
        If (_nodes.ContainsKey(uid)) Then Throw New ProviderException("Duplicate UID LogicNode")
        Dim questionId As Integer = reader.GetInt32(_indexQuestionID)
        Dim logic As String = reader.GetString(_indexLogic)
        Dim parentUid As Guid = reader.GetGuid(_indexParentUID)
        Dim node As New LogicNode(uid, parentUid, logic, questionId)
        node.SortID = reader.GetByte(_indexSortID)
        node.Enabled = reader.GetBoolean(_indexEnabled)
        _nodes.Add(uid, node)
        Return node
    End Function

    Private Function GetParentNodeFromDataReader(ByVal reader As SqlDataReader) As IQuestionNode
        'Make sure the parent ID is present
        If (reader.IsDBNull(_indexParentUID)) Then
            Throw New ProviderException("Missing parent UID")
        End If
        Dim parentUID As Guid = reader.GetGuid(_indexParentUID)

        'Make sure the parent ID is valid
        If (Not _nodes.ContainsKey(parentUID)) Then
            Throw New ProviderException("Invalid parent UID: " & parentUID.ToString)
        End If

        Return _nodes(parentUID)
    End Function

#End Region


    Public Overrides Function BuildQuestionnaireNode(ByVal questionnaireId As Integer) As QuestionnaireNode
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_QuestionnaireGet", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@Id", SqlDbType.BigInt, questionnaireId))
                Dim reader As SqlDataReader = Nothing
                Try
                    reader = cmd.ExecuteReader
                    If reader.Read Then
                        Dim qNode As New QuestionnaireNode(questionnaireId)
                        With qNode
                            .StudyID = Me.GetNullableString(reader, 1)
                            .InstrumentID = Me.GetNullableString(reader, 2)
                            .VisitNumber = reader.GetByte(3)
                            .Description = Me.GetNullableString(reader, 4)
                            .EdtingPermission = Me.GetNullableString(reader, 5)
                            .TakingPermission = Me.GetNullableString(reader, 6)
                            If Not reader.IsDBNull(7) Then
                                .CreatedDate = reader.GetDateTime(7).ToLocalTime
                            End If
                            .CreatedBy = Me.GetNullableString(reader, 8)
                            If Not reader.IsDBNull(9) Then
                                .LastUpdated = reader.GetDateTime(9).ToLocalTime
                            End If
                            .LastUpdatedBy = Me.GetNullableString(reader, 10)
                        End With
                        Return qNode
                    End If
                    Return Nothing
                Finally
                    If (Not reader Is Nothing) Then
                        reader.Close()
                    End If
                End Try
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw ex
            'Throw New ProviderException("Error getting instrument")
        End Try
    End Function

    Public Overrides Function BuildInstrumentNode( _
    ByVal instrumentId As String) As InstrumentNode
        Dim iNode As InstrumentNode
        ' Return immediately if this method has been called before
        If Me.InstrumentNodeTable.Contains(instrumentId) Then
            iNode = CType(Me.InstrumentNodeTable.Item(instrumentId), _
                InstrumentNode)
            Return iNode
        End If
        If Me.InstrumentNodeTable.Count = 0 Then
            Me.Clear()
        End If
        SyncLock _lock
            iNode = New InstrumentNode(instrumentId)
            FillInstrumentDetails(iNode)
            Try
                Dim conn As New SqlConnection(_connectionString)
                Try
                    conn.Open()
                    Dim cmd As New SqlCommand("dotnetpm_Questionnaire_InstrumentQuestionsGet", conn)
                    cmd.CommandTimeout = _CommandTimeout
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.Parameters.Add(Me.CreateInputParam("@InstrumentId", SqlDbType.VarChar, instrumentId))
                    Dim reader As SqlDataReader = Nothing
                    Try
                        reader = cmd.ExecuteReader
                        _indexUID = reader.GetOrdinal("UID")
                        _indexQuestionID = reader.GetOrdinal("QuestionID")
                        _indexInstrumentID = reader.GetOrdinal("InstrumentID")
                        _indexSortID = reader.GetOrdinal("SortID")
                        _indexEnabled = reader.GetOrdinal("Enabled")
                        If reader.Read Then
                            Dim _firstNode As IQuestionNode = CreateQuestionNodeFromDataReader(reader)
                            _firstNode.QuestionNumber = "1"
                            Me.FirstNodeTable.Item(instrumentId) = _firstNode
                            iNode.CurrentNode = _firstNode
                            AddNode(iNode, _firstNode, Nothing)
                            While reader.Read
                                Dim node As QuestionNode = CreateQuestionNodeFromDataReader(reader)
                                AddNode(iNode, node, Nothing)
                            End While
                        End If
                    Finally
                        If (Not reader Is Nothing) Then
                            reader.Close()
                        End If
                    End Try
                    Dim cmdLogics As New SqlCommand("dotnetpm_Questionnaire_InstrumentLogicNodesGet", conn)
                    cmdLogics.CommandTimeout = _CommandTimeout
                    cmdLogics.CommandType = CommandType.StoredProcedure
                    cmdLogics.Parameters.Add(Me.CreateInputParam("@InstrumentId", SqlDbType.VarChar, instrumentId))
                    Dim readerLogic As SqlDataReader = Nothing
                    Try
                        readerLogic = cmdLogics.ExecuteReader
                        _indexUID = readerLogic.GetOrdinal("UID")
                        _indexQuestionID = readerLogic.GetOrdinal("QuestionID")
                        _indexParentUID = readerLogic.GetOrdinal("ParentUID")
                        _indexSortID = readerLogic.GetOrdinal("SortID")
                        _indexLogic = readerLogic.GetOrdinal("Logic")
                        _indexEnabled = readerLogic.GetOrdinal("Enabled")
                        If readerLogic.Read Then
                            Dim node As LogicNode = CreateLogicNodeFromDataReader(readerLogic)
                            AddNode(iNode, node, GetParentNodeFromDataReader(readerLogic))
                            While readerLogic.Read
                                node = CreateLogicNodeFromDataReader(readerLogic)
                                AddNode(iNode, node, GetParentNodeFromDataReader(readerLogic))
                            End While
                        End If
                    Finally
                        If (Not readerLogic Is Nothing) Then
                            readerLogic.Close()
                        End If
                    End Try
                    If Me.InstrumentNodeTable.Contains(instrumentId) Then
                        Throw New InvalidOperationException("a duplicated questionnaire key is founded: " & instrumentId)
                    End If
                    Me.InstrumentNodeTable.Item(instrumentId) = iNode
                Finally
                    conn.Close()
                    conn = Nothing
                End Try
            Catch ex As Exception
                'Throw New ProviderException("Error getting Questionnaire Question")
                Throw ex
            End Try
        End SyncLock
        Return iNode
    End Function

    Public Overrides Function GetAnswerFromDB( _
        ByVal questionnaireId As Integer, _
        ByVal instrumentId As String, _
        ByVal userName As String) As Hashtable
        Dim htResult As New Hashtable
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_InstrumentAnswerTableGet", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@QuestionnaireId", SqlDbType.BigInt, questionnaireId))
                cmd.Parameters.Add(Me.CreateInputParam("@InstrumentId", SqlDbType.NVarChar, instrumentId))
                cmd.Parameters.Add(Me.CreateInputParam("@UserName", SqlDbType.NVarChar, userName))
                Dim reader As SqlDataReader = Nothing
                Try
                    reader = cmd.ExecuteReader
                    If reader.Read Then
                        For i As Integer = 5 To reader.FieldCount - 1
                            If Not reader.IsDBNull(i) Then
                                htResult(New Guid(reader.GetName(i))) _
                                    = reader.GetValue(i)
                            End If
                        Next
                    End If
                    Return htResult
                Finally
                    If (Not reader Is Nothing) Then
                        reader.Close()
                    End If
                End Try
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            'Throw New ProviderException("Error getting Questionnaire Question")
            Throw ex
        End Try
    End Function

    Public Overrides Function GetChoices(ByVal id As Integer) As System.Data.DataTable
        Dim ds As New DataSet
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                Dim cmd As SqlCommand
                cmd = New SqlCommand("dotnetpm_Questionnaire_QuestionChoicesGet", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@QuestionId", SqlDbType.Int, id))
                Dim da As New SqlDataAdapter
                da.SelectCommand = cmd
                conn.Open()
                da.Fill(ds, "Questionnaire_QuestionChoices")
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw ex
            'Throw New ProviderException("Error getting Questionnaire Choices")
        End Try
        Return ds.Tables(0)
    End Function

    Public Overrides Function GetQuestion(ByVal id As Integer) As Question
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_QuestionGet", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@Id", SqlDbType.Int, id))
                Dim reader As SqlDataReader = Nothing
                Try
                    reader = cmd.ExecuteReader
                    If reader.Read Then
                        Dim categoryId As Integer = reader.GetInt16(1)
                        Dim question As String = Me.GetNullableString(reader, 2)
                        Dim configuration As String = Me.GetNullableString(reader, 3)
                        Return New Question(id, question, configuration)
                    End If
                    Return Nothing
                Finally
                    If (Not reader Is Nothing) Then
                        reader.Close()
                    End If
                End Try
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            'Throw New ProviderException("Error getting Questionnaire Question")
            Throw ex
        End Try
    End Function

    Public Overrides Function GetQuestionnaires() As System.Data.DataTable
        Dim ds As New DataSet
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                Dim cmd As SqlCommand
                cmd = New SqlCommand("dotnetpm_Questionnaire_QuestionnairesGet", conn)
                cmd.Parameters.Add(CreateInputParam("@StudyId", SqlDbType.NVarChar, "Show All"))
                cmd.Parameters.Add(CreateInputParam("@InstrumentId", SqlDbType.NVarChar, "Show All"))
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                Dim da As New SqlDataAdapter
                da.SelectCommand = cmd
                conn.Open()
                da.Fill(ds, "Questionnaire_Questionnaires")
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw ex
            'Throw New ProviderException("Error getting Questionnaires")
        End Try
        Return ds.Tables(0)
    End Function

    Public Overrides Sub FillInstrumentDetails(ByRef node As InstrumentNode)
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_InstrumentGet", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@InstrumentId", SqlDbType.VarChar, node.InstrumentID))
                Dim reader As SqlDataReader = Nothing
                Try
                    reader = cmd.ExecuteReader
                    If reader.Read Then
                        With node
                            .Name = Me.GetNullableString(reader, 1)
                            .Description = Me.GetNullableString(reader, 2)
                            .Instruction = Me.GetNullableString(reader, 3)
                            If Not reader.IsDBNull(4) Then
                                .CreatedDate = reader.GetDateTime(4).ToLocalTime
                            End If
                            If Not reader.IsDBNull(5) Then
                                .LastUpdated = reader.GetDateTime(5).ToLocalTime
                            End If
                            .Locked = reader.GetBoolean(6)
                        End With
                    End If
                Finally
                    If (Not reader Is Nothing) Then
                        reader.Close()
                    End If
                End Try
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw ex
            'Throw New ProviderException("Error getting instrument")
        End Try
    End Sub

    Public Overrides Function GetPercentCompletion( _
    ByVal questionnaireId As Integer, _
    ByVal instrumentId As String) As String

        Dim userName As String = Web.HttpContext.Current.User.Identity.Name
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_InstrumentAnswerTableCompletionGet", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@QuestionnaireId", SqlDbType.BigInt, questionnaireId))
                cmd.Parameters.Add(Me.CreateInputParam("@InstrumentId", SqlDbType.NVarChar, instrumentId))
                cmd.Parameters.Add(Me.CreateInputParam("@UserName", SqlDbType.NVarChar, userName))
                Dim reader As SqlDataReader = Nothing
                Try
                    reader = cmd.ExecuteReader
                    Dim colStatus As Integer = reader.GetOrdinal("Status")
                    If reader.Read Then
                        Return Me.GetNullableString(reader, colStatus)
                    End If
                    Return "0.00 %"
                Finally
                    If (Not reader Is Nothing) Then
                        reader.Close()
                    End If
                End Try
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            'Throw New ProviderException("Error getting Questionnaire Question")
            Throw ex
        End Try
    End Function

    Public Overrides Sub SetAnswer( _
        ByVal questionnaireId As Integer, _
        ByVal instrumentId As String, _
        ByVal uid As Guid, _
        ByVal answer As String, _
        ByVal status As String)

        Dim userName As String = Web.HttpContext.Current.User.Identity.Name

        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_InstrumentAnswerTableSet", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                Dim curTime As DateTime = Me.RoundToSeconds(DateTime.UtcNow)
                cmd.Parameters.Add(Me.CreateInputParam("@QuestionnaireId", SqlDbType.BigInt, questionnaireId))
                cmd.Parameters.Add(Me.CreateInputParam("@InstrumentId", SqlDbType.NVarChar, instrumentId))
                cmd.Parameters.Add(Me.CreateInputParam("@UID", SqlDbType.NVarChar, uid.ToString))
                cmd.Parameters.Add(Me.CreateInputParam("@UserName", SqlDbType.NVarChar, userName))
                cmd.Parameters.Add(Me.CreateInputParam("@Answer", SqlDbType.NVarChar, answer))
                cmd.Parameters.Add(Me.CreateInputParam("@Status", SqlDbType.NVarChar, status))
                cmd.Parameters.Add(Me.CreateInputParam("@UtcLastUpdated", SqlDbType.NVarChar, curTime))
                Dim p1 = New SqlParameter("@ReturnValue", SqlDbType.Int)
                p1.Direction = ParameterDirection.ReturnValue
                cmd.Parameters.Add(p1)
                cmd.ExecuteNonQuery()
                Dim result As Integer = IIf((p1.Value Is Nothing), -1, CType(p1.Value, Integer))
                If result <> 0 Then
                    Throw New ProviderException("Result <> 0.")
                End If
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            'Throw New ProviderException("Failed to update the instrument.")
            Throw ex
        End Try
    End Sub

End Class
